import json
from fastapi import Depends, APIRouter
from pydantic import BaseModel

from routes.slot_management import sql_polars, sqlpyodbc
from util.JWT import get_current_user


assign_slot = APIRouter()


def merge_lists(list1, list2):
    # 创建一个字典，用于存储列表2中的数据，键为LocationID
    location_data = {}
    for item in list2:
        location_id = item['LocationID']
        if location_id not in location_data:
            location_data[location_id] = []
        location_data[location_id].append({k: v for k, v in item.items() if k != 'LocationID'})

    # 遍历列表1，如果LocationID在location_data中，则添加family键
    for item in list1:
        location_id = item['LocationID']
        if location_id in location_data:
            item['family'] = location_data[location_id]

    return list1
# 刷新

class getassignType(BaseModel):
    ContractNo: str
@assign_slot.post("/getassign")
async def getassign(data: getassignType, id: dict = Depends(get_current_user)):
    sql = ''
    if data.ContractNo == "":
        sql = "SELECT ConCaseNo,ContractNo,CaseNo,B.CN_Name,B.EN_Name,Size,PackageType_C,LocationID,G_Weight FROM CaseResult AS A LEFT JOIN Country AS B ON A.CountryID=B.CountryID WHERE PackageQtyStatus=1 AND LocationID='' AND isout=0"
    else:
        sql = f"SELECT ConCaseNo,ContractNo,CaseNo,B.CN_Name,B.EN_Name,Size,PackageType_C,LocationID,G_Weight FROM CaseResult AS A LEFT JOIN Country AS B ON A.CountryID=B.CountryID WHERE PackageQtyStatus=1 AND ContractNo='{data.ContractNo}'"
    df = sql_polars.query(sql)
    if df.height < 1:
        return {
            "code": 204,
            "status": "no",
            "msg": "没有内容",
            "data": []
        }
    df = df.to_dicts()
    json_string = json.dumps(df, ensure_ascii=False)
    return {
        "code": 200,
        "status": "ok",
        "msg": "success",
        "data": json_string
    }


@assign_slot.get("/getLocation")
async def getLocation(id: dict = Depends(get_current_user)):
    sql = """
    SELECT 
    sl.LocationID,
    sl.MaxPallets,
    ISNULL(sl.MaxPallets, 0) - ISNULL(cr.use_qty, 0) AS RemCap,
    sl.Note
FROM 
    StorageLocation sl
LEFT JOIN 
    (SELECT 
        LocationID,
        COUNT(LocationID) AS use_qty 
     FROM 
        CaseResult 
     WHERE 
        isout = 0 AND LocationID != ''
     GROUP BY 
        LocationID
    ) cr ON sl.LocationID = cr.LocationID
    """

    sql1="SELECT  LocationID,ContractNo,CaseNo,Size,PackageType_C FROM CaseResult WHERE isout = 0 AND LocationID != ''"
    df = sql_polars.query(sql)
    if df.height < 1:
        return {
            "code": 204,
            "status": "no",
            "msg": "没有内容",
            "data": []
        }
    df = df.to_dicts()
    df1 = sql_polars.query(sql1)
    if df1.height > 1:
        df1=df1.to_dicts()
        df = merge_lists(df, df1)
    json_string = json.dumps(df, ensure_ascii=False)
    return {
        "code": 200,
        "status": "ok",
        "msg": "success",
        "data": json_string
    }


# 分配货位
class allotType(BaseModel):
    conCaseNo: str
    locationID: str
@assign_slot.post("/allot_loction")
async def allot_loction(data: allotType, id: dict = Depends(get_current_user)):
    sql = f"UPDATE CaseResult SET LocationID='{data.locationID}' WHERE ConCaseNo='{data.conCaseNo}'"
    try:
        if sqlpyodbc.execute(sql) > 0:
            return {"code": 200, "status": "ok", "msg": "success"}
        return {"code": 400, "status": "no", "msg": "error"}
    except:
        return {"code": 400, "status": "no", "msg": "error"}


# 修改货位
class updateType(BaseModel):
    conCaseNo: str
    newlocationID: str


@assign_slot.post("/update_loction")
async def update_loction(data: updateType, id: dict = Depends(get_current_user)):
    sql = f"UPDATE CaseResult SET LocationID='{data.newlocationID}' WHERE ConCaseNo='{data.conCaseNo}'"
    try:
        if sqlpyodbc.execute(sql) > 0:
            return {"code": 200, "status": "ok", "msg": "修改成功"}
        return {"code": 400, "status": "no", "msg": "修改失败"}
    except:
        return {"code": 400, "status": "no", "msg": "error"}
